package com.example.sqliteviewer;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.Environment;
import android.widget.Toast;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

/**
 * 数据导出工具类
 */
public class ExportUtils {

    /**
     * 将表数据导出为CSV文件
     *
     * @param context      上下文
     * @param databasePath 数据库路径
     * @param tableName    表名
     * @param searchQuery  搜索条件（可为空）
     * @return 导出的文件路径，如果导出失败则返回null
     */
    public static String exportTableToCSV(Context context, String databasePath, String tableName, String searchQuery) {
        SQLiteDatabase database = null;
        Cursor cursor = null;
        FileWriter fileWriter = null;
        
        try {
            // 打开数据库
            database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READONLY);
            
            // 获取表结构
            Cursor structureCursor = database.rawQuery("PRAGMA table_info(" + tableName + ")", null);
            
            if (!structureCursor.moveToFirst()) {
                Toast.makeText(context, "无法获取表结构", Toast.LENGTH_SHORT).show();
                structureCursor.close();
                return null;
            }
            
            // 获取列名
            int columnCount = structureCursor.getCount();
            String[] columnNames = new String[columnCount];
            int index = 0;
            
            do {
                String columnName = structureCursor.getString(structureCursor.getColumnIndex("name"));
                columnNames[index++] = columnName;
            } while (structureCursor.moveToNext());
            
            structureCursor.close();
            
            // 构建查询语句
            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.append("SELECT * FROM ").append(tableName);
            
            // 如果有搜索条件，添加WHERE子句
            if (searchQuery != null && !searchQuery.isEmpty()) {
                queryBuilder.append(" WHERE ");
                
                for (int i = 0; i < columnNames.length; i++) {
                    if (i > 0) {
                        queryBuilder.append(" OR ");
                    }
                    queryBuilder.append(columnNames[i]).append(" LIKE '%").append(searchQuery).append("%'");
                }
            }
            
            // 执行查询
            cursor = database.rawQuery(queryBuilder.toString(), null);
            
            if (cursor.getCount() == 0) {
                Toast.makeText(context, "没有数据可导出", Toast.LENGTH_SHORT).show();
                return null;
            }
            
            // 创建导出目录
            File exportDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS), "SQLiteViewer");
            if (!exportDir.exists()) {
                exportDir.mkdirs();
            }
            
            // 创建导出文件
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss", Locale.getDefault());
            String timestamp = dateFormat.format(new Date());
            String fileName = tableName + "_" + timestamp + ".csv";
            File exportFile = new File(exportDir, fileName);
            
            fileWriter = new FileWriter(exportFile);
            
            // 写入列名（CSV头）
            StringBuilder headerBuilder = new StringBuilder();
            for (int i = 0; i < columnNames.length; i++) {
                if (i > 0) {
                    headerBuilder.append(",");
                }
                headerBuilder.append(escapeCSV(columnNames[i]));
            }
            fileWriter.write(headerBuilder + "\n");
            
            // 写入数据行
            if (cursor.moveToFirst()) {
                do {
                    StringBuilder rowBuilder = new StringBuilder();
                    for (int i = 0; i < columnNames.length; i++) {
                        if (i > 0) {
                            rowBuilder.append(",");
                        }
                        
                        String value = "";
                        if (!cursor.isNull(i)) {
                            switch (cursor.getType(i)) {
                                case Cursor.FIELD_TYPE_INTEGER:
                                    value = String.valueOf(cursor.getLong(i));
                                    break;
                                case Cursor.FIELD_TYPE_FLOAT:
                                    value = String.valueOf(cursor.getDouble(i));
                                    break;
                                case Cursor.FIELD_TYPE_STRING:
                                    value = cursor.getString(i);
                                    break;
                                case Cursor.FIELD_TYPE_BLOB:
                                    value = "[BLOB]";
                                    break;
                                case Cursor.FIELD_TYPE_NULL:
                                    value = "";
                                    break;
                            }
                        }
                        
                        rowBuilder.append(escapeCSV(value));
                    }
                    fileWriter.write(rowBuilder + "\n");
                    
                } while (cursor.moveToNext());
            }
            
            return exportFile.getAbsolutePath();
            
        } catch (Exception e) {
            e.printStackTrace();
            Toast.makeText(context, "导出失败: " + e.getMessage(), Toast.LENGTH_SHORT).show();
            return null;
        } finally {
            // 关闭资源
            if (cursor != null) {
                cursor.close();
            }
            if (database != null) {
                database.close();
            }
            if (fileWriter != null) {
                try {
                    fileWriter.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * 转义CSV字段值
     * CSV格式规则：如果字段包含逗号、双引号或换行符，则需要用双引号包围，并将字段中的双引号替换为两个双引号
     */
    private static String escapeCSV(String value) {
        if (value == null) {
            return "";
        }
        
        boolean needQuotes = value.contains(",") || value.contains("\"") || value.contains("\n") || value.contains("\r");
        
        if (needQuotes) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        } else {
            return value;
        }
    }
    
    /**
     * 将文件复制到指定的Uri位置
     *
     * @param context 上下文
     * @param sourceFile 源文件
     * @param destUri 目标Uri
     * @return 是否复制成功
     */
    public static boolean copyFileToUri(Context context, File sourceFile, Uri destUri) {
        InputStream inputStream = null;
        OutputStream outputStream = null;
        
        try {
            inputStream = new FileInputStream(sourceFile);
            outputStream = context.getContentResolver().openOutputStream(destUri);
            
            if (outputStream == null) {
                return false;
            }
            
            byte[] buffer = new byte[4096];
            int bytesRead;
            
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            
            return true;
            
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}